﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MarginTrading.Entity;
using System.Data;
using System.Data.OracleClient;
using MarginTrading.Common;

namespace MarginTrading.Services
{
    public static class ReportServices
    {
        public static DataTable GetPortfolioValuation1(string clientId)
        {
            DataTable retVal = new DataTable();
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SECURITY.SYMBOL AS STOCKCODE," +
                                   "NAME AS SHAREDESCRIPTION, " +
                                   "QUANTITY,MARKET_VALUE AS MKTPRICE, " +
                                   "(QUANTITY * MARKET_VALUE) AS MKTVALUE,MARGIN_PERCENTAGE AS ALLOWED," +
                                   "((QUANTITY * MARKET_VALUE * MARGIN_PERCENTAGE)/100) AS MTMKTVALUE " +
                                   "FROM PORTFOLIO INNER JOIN SECURITY " +
                                   "ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                   "WHERE PORTFOLIO.CODE = '" + clientId + "' ORDER BY PORTFOLIO.CODE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }

        public static DataTable GetTransactionsCollectionDataTableByClientIdAndDateRange(string clientId, DateTime fromDate, DateTime toDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select trade_date as TRADEDATE, " +
                                        "BUY_SELL as TRTYPE, " +
                                        "TRAN_ID as REFERENCE, " +
                                        "QUANTITY as NUMBEROFSHARES, " +
                                        "PRICE, " +
                                        "AMOUNT, " +
                                        "COMPANY_SYMBOL as PARTICULARS, " +
                                        "'' as BALANCE, " +
                                        "SETTLEMENT_DATE as SETTLEMENTDATE, " +
                                        "(CASE WHEN BUY_SELL = 'B' THEN (AMOUNT) ELSE 0 END) as PURCHASETURNOVER, " +
                                        "(CASE WHEN BUY_SELL = 'S' THEN (AMOUNT) ELSE 0 END) as SALESTURNOVER " +
                                        //"(CASE WHEN BUY_SELL = 'B' THEN (AMOUNT) ELSE 0 END) as TOTALRECEIPTS, " +
                                        //"(CASE WHEN BUY_SELL = 'B' THEN (AMOUNT) ELSE 0 END) as TOTALPAYMENTS " +
                                        "FROM DAILY_TRANSACTIONS " +
                                        "where CLIENT_CODE = :v1 " +
                                        "and TRADE_DATE >= :v2 " +
                                        "and TRADE_DATE <= :v3 "+
                                        "UNION ALL " +
                                        "SELECT CREATED_DATE as TRADEDATE," +
                                        "ENTRY_TYPE as TRTYPE," +
                                        "REFERENCE_NO as REFERENCE," +
                                        "0 as NUMBEROFSHARES," +
                                        "0 as PRICE," +
                                        "AMOUNT," +
                                        "TRANSACTION_PARTICULARS as PARTICULARS," +
                                        "'' as BALANCE, " +
                                        "null as SETTLEMENTDATE," +
                                        "0 as PURCHASETURNOVER," +
                                        "0 as SALESTURNOVER " +
                                        "FROM LOAN_ACCOUNT " +
                                        "where CLIENT_CODE = :v1 " +
                                        "and CREATED_DATE >= :v2 " +
                                        "and CREATED_DATE <= :v3 and entry_type in ('PA','RC') order by TRADEDATE";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = fromDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = toDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetLoanAccountCollectionDataTableByClientIdAndDateRange(string clientId, DateTime fromDate, DateTime toDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CREATED_DATE as LOANACCOUNTDATE, " +
                                        "LOAN_ACCOUNT.ENTRY_TYPE as TRXTYPE, " +
                                        "TRANSACTION_PARTICULARS as TRANSACTIONPARTICULARS, " +
                                        "REFERENCE_NO as REFERENCENUMBER, " +
                                        //"BALANCE, " +
                                        "AMOUNT, " +
                                        "(CASE WHEN AMOUNT < 0 THEN (AMOUNT*-1) ELSE 0 END) as RECEIPTS, " +
                                        "(CASE WHEN AMOUNT > 0 THEN (AMOUNT) ELSE 0 END) as PAYMENTS " +
                                        "FROM LOAN_ACCOUNT " +
                                        "WHERE CLIENT_CODE = :v1 " +
                                        "AND CREATED_DATE >= :v2 " +
                                        "AND CREATED_DATE <= :v3 ORDER BY CREATED_DATE";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = fromDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = toDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatusByAdvisor(string advisorId)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, "+
                                  "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                  "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                  "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                  "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                  "(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE) as LOFINBALANCE, " +
                                  "((SELECT SUM(AMOUNT) FROM DAILY_TRANSACTIONS WHERE DAILY_TRANSACTIONS.CLIENT_CODE = CLIENT.CODE)+(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND entry_type in ('PA','RC'))) as NETFETUREBAL, " +
                                  "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST " +
                                  //"(((LOFINBALANCE + PENDINGINTEREST-(NETFETUREBAL * -1)) / (MTPORTFOLIOVALUE))*100) as MARGIN " +
                                  "FROM CLIENT "+
                                  //"INNER JOIN PORTFOLIO "+
                                  //"ON CLIENT.CODE = PORTFOLIO.CODE " +
                                  "where ADVISOR = :v1 AND MARGIN_CLIENT = 1";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisorId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatusByAdvisor()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, " +
                                  "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                  "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                  "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                  "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                  "(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE) as LOFINBALANCE, " +
                                  "((SELECT SUM(AMOUNT) FROM DAILY_TRANSACTIONS WHERE DAILY_TRANSACTIONS.CLIENT_CODE = CLIENT.CODE)+(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND entry_type in ('PA','RC'))) as NETFETUREBAL, " +
                                  "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST " +
                    //"(((LOFINBALANCE + PENDINGINTEREST-(NETFETUREBAL * -1)) / (MTPORTFOLIOVALUE))*100) as MARGIN " +
                                  "FROM CLIENT WHERE MARGIN_CLIENT = 1";// +
                    //"INNER JOIN PORTFOLIO "+
                    //"ON CLIENT.CODE = PORTFOLIO.CODE " +
                    //              "where ADVISOR = :v1 ";

                //cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisorId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatus(string clientCode, string clientCategory)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                if (clientCode == "All")
                {
                    if (clientCategory == "All")
                    {
                        cmd.CommandText = "SELECT CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, CLIENT.CODE as CLIENTCODE, CLIENT.BANK_ACC_NO as BANKACCNO, " +
                                          "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                          "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                          "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                          "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                          "(SELECT (CASE WHEN DEBIT_OR_CREDIT = 'D' THEN BALANCE ELSE BALANCE*-1 END) FROM CURRENT_ACC_BALANCE WHERE (CURRENT_ACC_BALANCE.ACC_NO = CLIENT.BANK_ACC_NO AND " +
                                          "BAL_DATE = (SELECT MAX(BAL_DATE) FROM CURRENT_ACC_BALANCE WHERE ACC_NO = CLIENT.BANK_ACC_NO ))) as LOFINBALANCE, " +
                                          "((SELECT SUM(AMOUNT) FROM DAILY_TRANSACTIONS WHERE DAILY_TRANSACTIONS.CLIENT_CODE = CLIENT.CODE) + " +
                                          "(CASE WHEN (SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND "+
                                          "entry_type in ('PA','RC')) IS NULL THEN 0 ELSE (SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND "+
                                          "entry_type in ('PA','RC')) END)) as NETFETUREBAL, " +
                                          "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST " +
                                          "FROM CLIENT " +
                                          "INNER JOIN CURRENT_ACC_BALANCE ON CURRENT_ACC_BALANCE.ACC_NO = CLIENT.BANK_ACC_NO " +
                                          "where MARGIN_CLIENT = 1 AND BAL_DATE = (SELECT MAX(BAL_DATE) FROM CURRENT_ACC_BALANCE WHERE ACC_NO = CLIENT.BANK_ACC_NO )";
                    }
                    else
                    {
                        cmd.CommandText = "SELECT CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, CLIENT.CODE as CLIENTCODE, CLIENT.BANK_ACC_NO as BANKACCNO, " +
                                          "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                          "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                          "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                          "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                          "(SELECT (CASE WHEN DEBIT_OR_CREDIT = 'D' THEN BALANCE ELSE BALANCE*-1 END) FROM CURRENT_ACC_BALANCE WHERE (CURRENT_ACC_BALANCE.ACC_NO = CLIENT.BANK_ACC_NO AND " +
                                          "BAL_DATE = (SELECT MAX(BAL_DATE) FROM CURRENT_ACC_BALANCE WHERE ACC_NO = CLIENT.BANK_ACC_NO ))) as LOFINBALANCE, " +
                                          "((SELECT SUM(AMOUNT) FROM DAILY_TRANSACTIONS WHERE DAILY_TRANSACTIONS.CLIENT_CODE = CLIENT.CODE) + "+
                                          "(CASE WHEN (SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND "+
                                          "entry_type in ('PA','RC')) IS NULL THEN 0 ELSE (SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND "+
                                          "entry_type in ('PA','RC')) END)) as NETFETUREBAL, " +
                                          "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST " +
                                          "FROM CLIENT " +
                                          "INNER JOIN CURRENT_ACC_BALANCE ON CURRENT_ACC_BALANCE.ACC_NO = CLIENT.BANK_ACC_NO " +
                                          "where MARGIN_CLIENT = 1 AND CLIENT.CATEGORY = :v2 AND BAL_DATE = (SELECT MAX(BAL_DATE) FROM CURRENT_ACC_BALANCE WHERE ACC_NO = CLIENT.BANK_ACC_NO )";

                        cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCategory;
                    }
                }
                else
                {
                    cmd.CommandText = "SELECT CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, CLIENT.CODE as CLIENTCODE, CLIENT.BANK_ACC_NO as BANKACCNO, " +
                                          "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                          "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                          "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                          "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                          "(SELECT (CASE WHEN DEBIT_OR_CREDIT = 'D' THEN BALANCE ELSE BALANCE*-1 END) FROM CURRENT_ACC_BALANCE WHERE CURRENT_ACC_BALANCE.ACC_NO = CLIENT.BANK_ACC_NO AND " +
                                          "BAL_DATE = (SELECT MAX(BAL_DATE) FROM CURRENT_ACC_BALANCE WHERE ACC_NO = CLIENT.BANK_ACC_NO )) as LOFINBALANCE, " +
                                          "((SELECT SUM(AMOUNT) FROM DAILY_TRANSACTIONS WHERE DAILY_TRANSACTIONS.CLIENT_CODE = CLIENT.CODE)+(CASE WHEN (SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND entry_type in ('PA','RC')) IS NULL THEN 0 ELSE (SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE AND entry_type in ('PA','RC')) END)) as NETFETUREBAL, " +
                                          "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST " +
                                          "FROM CLIENT " +
                                          "INNER JOIN CURRENT_ACC_BALANCE ON CURRENT_ACC_BALANCE.ACC_NO = CLIENT.BANK_ACC_NO " +
                                          "where MARGIN_CLIENT = 1 AND CLIENT.CODE = :v1 AND BAL_DATE = (SELECT MAX(BAL_DATE) FROM CURRENT_ACC_BALANCE WHERE ACC_NO = CLIENT.BANK_ACC_NO )";

                        cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientCode;
                }

                
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetTop3Days(string agent, DateTime date)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT TO_CHAR(TRADE_DATE,'yyyy/mm/dd') FROM DAILY_TRANSACTIONS " + 
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE "+
                                  "where CLIENT.AGENT = :v1 and TRADE_DATE <= :v2 GROUP BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') " +
                                  "ORDER BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') DESC";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetTop3DaysByClient(string client, DateTime date)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT TO_CHAR(TRADE_DATE,'yyyy/mm/dd') FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "where CLIENT.CODE = :v1 and TRADE_DATE <= :v2 GROUP BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') " +
                                  "ORDER BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') DESC";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = client;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetTop3Days(DateTime date)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT TO_CHAR(TRADE_DATE,'yyyy/mm/dd') FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "where TRADE_DATE <= :v1 GROUP BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') " +
                                  "ORDER BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') DESC";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetTop3DaysByClientCategory(string clentCategory, DateTime date)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                if (clentCategory == "All")
                {
                    cmd.CommandText = "SELECT TO_CHAR(TRADE_DATE,'yyyy/mm/dd') FROM DAILY_TRANSACTIONS " +
                                      "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                      "where TRADE_DATE <= :v1 GROUP BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') " +
                                      "ORDER BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') DESC";
                    cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                }
                else
                {
                    cmd.CommandText = "SELECT TO_CHAR(TRADE_DATE,'yyyy/mm/dd') FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "where TRADE_DATE <= :v1 AND CLIENT.CATEGORY = :v2 GROUP BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') " +
                                  "ORDER BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') DESC";
                    cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                    cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clentCategory;
                }

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DateTime GetSettlementDate(DateTime date)
        {
            DateTime retVal = DateTime.MinValue;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') as TRADE_DATE FROM DAILY_TRANSACTIONS " +
                                  "where TO_CHAR(TRADE_DATE,'mm/dd/yyyy') =  TO_CHAR(:v1,'mm/dd/yyyy') GROUP BY TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') ";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        DateTime.TryParse(dataReader["TRADE_DATE"].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetDebtorsFollowupByAgentAndDate(string agent, DateTime date1, DateTime date2, DateTime date3)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || "+
                                  "CLIENT.SURNAME as CLIENTNAME, "+
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as BUYDATE1, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE1 " +
                                  "from DAILY_TRANSACTIONS "+
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE "+
                                  "WHERE CLIENT.AGENT = :v1 AND TO_CHAR(TRADE_DATE,'mm/dd/yyyy') IN (TO_CHAR(:v2,'mm/dd/yyyy'),TO_CHAR(:v3,'mm/dd/yyyy'),TO_CHAR(:v4,'mm/dd/yyyy'))" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.OTHER_NAMES,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = date2;
                cmd.Parameters.Add(":v4", OracleType.DateTime).Value = date3;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetDebtorsFollowupByClientAndDate(string client, DateTime date1, DateTime date2, DateTime date3)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as BUYDATE1, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE1 " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE CLIENT.CODE = :v1 AND TO_CHAR(TRADE_DATE,'mm/dd/yyyy') IN (TO_CHAR(:v2,'mm/dd/yyyy'),TO_CHAR(:v3,'mm/dd/yyyy'),TO_CHAR(:v4,'mm/dd/yyyy'))" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.OTHER_NAMES,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = client;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = date2;
                cmd.Parameters.Add(":v4", OracleType.DateTime).Value = date3;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetDebtorsFollowupByAgentAndDate(DateTime date1, DateTime date2, DateTime date3)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as BUYDATE1, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE1 " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE TO_CHAR(TRADE_DATE,'mm/dd/yyyy') IN (TO_CHAR(:v2,'mm/dd/yyyy'),TO_CHAR(:v3,'mm/dd/yyyy'),TO_CHAR(:v4,'mm/dd/yyyy'))" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.OTHER_NAMES,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = date2;
                cmd.Parameters.Add(":v4", OracleType.DateTime).Value = date3;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetDebtorsFollowupByClientCategoryAndDate(string clentCategory, DateTime date1, DateTime date2, DateTime date3)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                if (clentCategory == "All")
                {
                    cmd.CommandText = "select CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as BUYDATE1, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE1 " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE TO_CHAR(TRADE_DATE,'mm/dd/yyyy') IN (TO_CHAR(:v2,'mm/dd/yyyy'),TO_CHAR(:v3,'mm/dd/yyyy'),TO_CHAR(:v4,'mm/dd/yyyy'))" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.OTHER_NAMES,CLIENT.SURNAME,CLIENT.AGENT";

                    cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                    cmd.Parameters.Add(":v3", OracleType.DateTime).Value = date2;
                    cmd.Parameters.Add(":v4", OracleType.DateTime).Value = date3;
                }
                else
                {
                    cmd.CommandText = "select CLIENT.INITIALS || ' ' || " +
                                      "CLIENT.SURNAME as CLIENTNAME, " +
                                      "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE3, " +
                                      "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE2, " +
                                      "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as BUYDATE1, " +
                                      "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE3, " +
                                      "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE2, " +
                                      "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE1 " +
                                      "from DAILY_TRANSACTIONS " +
                                      "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                      "WHERE CLIENT.CATEGORY = :v1 AND TO_CHAR(TRADE_DATE,'mm/dd/yyyy') IN (TO_CHAR(:v2,'mm/dd/yyyy'),TO_CHAR(:v3,'mm/dd/yyyy'),TO_CHAR(:v4,'mm/dd/yyyy'))" +
                                      "GROUP BY CLIENT.INITIALS,CLIENT.OTHER_NAMES,CLIENT.SURNAME,CLIENT.AGENT";

                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clentCategory;
                    cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                    cmd.Parameters.Add(":v3", OracleType.DateTime).Value = date2;
                    cmd.Parameters.Add(":v4", OracleType.DateTime).Value = date3;
                }

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetBSSummaryByAgentAndDate(string clientCategory, DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "(sum(CASE WHEN BUY_SELL = 'B' THEN AMOUNT*-1 ELSE 0 END)) as AMOUNT, " +
                                  "COUNT(*)*25 as CDSCHARG, " +
                                  "(COUNT(*)*25 + sum(CASE WHEN BUY_SELL = 'B' THEN AMOUNT*-1 ELSE 0 END)) as TOTAL " +
                                  "FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE CLIENT.CATEGORY = :v1 AND TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' AND CLIENT.MARGIN_CLIENT = 1" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientCategory;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetBSSummaryByAgentAndDate(DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "(sum(CASE WHEN BUY_SELL = 'B' THEN AMOUNT*-1 ELSE 0 END)) as AMOUNT, " +
                                  "COUNT(*)*25 as CDSCHARG, " +
                                  "(COUNT(*)*25 + sum(CASE WHEN BUY_SELL = 'B' THEN AMOUNT*-1 ELSE 0 END)) as TOTAL " +
                                  "FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' AND CLIENT.MARGIN_CLIENT = 1" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME";

                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetSSSummaryByAgentAndDate(string clientCategory, DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "(sum(CASE WHEN BUY_SELL = 'S' THEN AMOUNT ELSE 0 END)) as AMOUNT, " +
                                  "COUNT(*)*25 as CDSCHARG, " +
                                  "(sum(CASE WHEN BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) - COUNT(*)*25) as TOTAL " +
                                  "FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE CLIENT.CATEGORY = :v1 AND TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' AND CLIENT.MARGIN_CLIENT = 1 " +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientCategory;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetSSSummaryByAgentAndDate(DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "(sum(CASE WHEN BUY_SELL = 'S' THEN AMOUNT ELSE 0 END)) as AMOUNT, " +
                                  "COUNT(*)*25 as CDSCHARG, " +
                                  "(sum(CASE WHEN BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) - COUNT(*)*25) as TOTAL " +
                                  "FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' AND CLIENT.MARGIN_CLIENT = 1" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME";

                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetInterestByClientAndDateRange(string clientCode, DateTime fromDate, DateTime toDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT INTEREST_DATE as INTERESTDATE, " +
                                  "'PN' as TRXTYPE, " +
                                  "'IN/DELAYED' as REFERENCENUMBER, " +
                                  "'Int On Delayed Payment' as TRANSACTIONPARTICULARS, " +
                                  "INTEREST as INTEREST, " +
                                  "(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE CLIENT_CODE = :v1 AND LOAN_ACCOUNT.CREATED_DATE < DAILY_INTEREST.INTEREST_DATE + 1) as SETTLEMENTBALANCE " +
                                  "FROM DAILY_INTEREST " +
                                  "WHERE CLIENT_CODE = :v1 AND INTEREST_DATE >= :v2 AND INTEREST_DATE <= :v3 ORDER BY INTEREST_DATE";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientCode;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = fromDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = toDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static double GetCumInterest(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE INTEREST_DATE < :v1 AND CLIENT_CODE = :v2";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static double GetCumLoanAccount(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE CREATED_DATE < :v1 AND CLIENT_CODE = :v2";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static double GetAccountBalance(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(AMOUNT) FROM DAILY_TRANSACTIONS WHERE TRADE_DATE < :v1 AND CLIENT_CODE = :v2";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static double GetLoanAccountBalance(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE CREATED_DATE < :v1 AND CLIENT_CODE = :v2 AND entry_type in ('PA','RC')";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }
    }
}
